MDX with Microsoft SQL Server 2016 Analysis Services Cookbook Third Edition by Piasevoli Tomislav & Li Sherry

Author:Piasevoli, Tomislav & Li, Sherry
Language: eng
Format: epub
Publisher: Packt Publishing
Published: 2016-11-30T00:00:00+00:00

Define the set Hierarchy Set as a set of all hierarchy members: SET [Hierarchy Set] AS [Sales Territory].[Sales Territory].Members

Include all three calculated measures on columns and execute the query.

Verify that the result matches the following screenshot:

How it works...

The Rank() function has two variants, one with two arguments, and one with a third:

Rank(Member_Expression, Set_Expression) Rank(Member_Expression, Set_Expression, Numeric Expression)

When using the first syntax, where a numeric expression is not specified, the Rank() function simply returns the one-based ordinal position of the first member in the second set argument.

When using the second syntax, where a third numeric expression is specified, the Rank() function determines the one-based rank for the specified member in the set according to the results of evaluating the specified numeric expression against the member.

In our example, we have used the second syntax. Let us discuss some specifics of the second syntax and how we used it in our example.

When the third numeric argument is supplied to the Rank() function, the Rank() function evaluates the numeric expression for the member specified as the first argument and compares it to the members of the set specified as the second argument. The function then determines the 1-based rank of the member in that set according to the results.

All three ranks are for the current member on the rows. Therefore, we have used the same member expression as the first argument to the Rank() function:

[Sales Territory].[Sales Territory].CurrentMember

We have also provided the same numeric expression, [Measures].[Reseller Sales Amount], to the Rank() function as the third argument.

The only difference in the three calculations is in the second set expression we provided to the Rank() function.

The Siblings Rank is calculated against the current member's siblings, or children of the same parent. This is the set expression to get all the current member's siblings:

[Sales Territory].[Sales Territory].CurrentMember.Siblings

The Level Rank is calculated against all members in the same level as the current member's level. This is the set expression to get all members at the current member's level:

[Sales Territory].[Sales Territory].CurrentMember.Level.Members

Finally, the third rank, the Hierarchy Rank, is calculated against all members in that hierarchy. This is the set expression to get all the members in the hierarchy:

[Sales Territory].[Sales Territory].Members

That is also the only set not dependent on the current context. Notice that we have created a named set, [Hierarchy Set]. By creating a named set that is independent of the current context, we have essentially moved it outside the iteration. The query performance has been improved. Notice that we did not do this for the Siblings Rank and the Level Rank because we couldn't.

The rules are relatively simple. Whenever there's a set that doesn't depend on the current context, it is better to extract it from the calculation and define it as a named set. That way, it will be evaluated only once, after the evaluation of the subselect and slicer, and before the evaluation of axes. During the process of cell evaluation, which is the next phase in the query execution, such a set acts like a constant, which makes the calculations run faster.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.